/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package testonlineconnectdb;

import java.io.File;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;
import java.util.Random;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.xml.sax.SAXException;

/**
 *
 * @author AngelKid
 */
public class TestOnlineConnectDB {
    private Connection con;
    //public TestOnlineConnectDB(){};
    public Connection Connect(String pathDatabase) throws ClassNotFoundException, SQLException
    {
        //java.sql.DriverManager.setLogWriter(new PrintWriter(System.out));
        Class.forName("jstels.jdbc.mdb.MDBDriver2");
        Properties props = new Properties();
        //props.put("charset","UTF-8");
        //Connection con = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="+pathDatabase,props);
        con = DriverManager.getConnection("jdbc:jstels:mdb:"+pathDatabase);
        System.out.println("Connected");
        return con;
    }
    
    public Connection Connect1() throws ClassNotFoundException, SQLException
    {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/TestOnlineDB","sa","123456");
            System.out.println("connected1");
            return con;        
    }
    
    public ArrayList GetType() throws SQLException
    {
        ArrayList data = new ArrayList();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("Select * from LoaiDe");
        while(rs.next())
        {
            TestType temp = new TestType();
            temp.name = rs.getString("TenLoai");
            temp.time = rs.getInt("ThoiGian");
            temp.numEasy = rs.getInt("SLDe");
            temp.numNormal = rs.getInt("SLTB");
            temp.numDifficult = rs.getInt("SLKho");
            temp.code = rs.getInt("MaLoai");
            data.add(temp);
        }
        return data;
    
    }
    public ArrayList GetExamQuestionsByCode(int testCode,String pathXML) throws SQLException, IOException
    {
        ArrayList d = new ArrayList();
        Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE
                    ,ResultSet.CONCUR_UPDATABLE);
        Random r = new Random();
        ResultSet rs = st.executeQuery("Select * from DeThi where MaDe = "+testCode);
        rs.next();
        int testType = rs.getInt("MaLoai");
        String[] qIndex = (rs.getString("CauHoi")).split(" ");
        for(int i = 0; i < qIndex.length; i++)
        {
            System.out.println(Integer.parseInt(qIndex[i]));
        }
        SaxProcessGetQuestion sax=new SaxProcessGetQuestion(qIndex);//tao mot doi tuong cua saxprocess. 
        SAXParserFactory saxpaser=SAXParserFactory.newInstance(); 
        try { 
            SAXParser parser = saxpaser.newSAXParser(); 
            File F=new File(pathXML); 
            parser.parse(F, sax); 
        } catch (ParserConfigurationException ex) { 
            
        } catch (SAXException ex) { 
            
        }  
        ArrayList questions = sax.GetQuestion();
        if(questions == null)
        {
            System.out.println("Not Found");
            return null;
        }

        int fCount = 0;
        for(int i = 0; i < qIndex.length; i++)
            for(int j = 0;j < questions.size(); j++)
            {
                if(j==i)
                    continue;
                Question temp1 = (Question) questions.get(j);
                if(qIndex[i].equals(temp1.code))
                {
                    Question temp2 = (Question) questions.get(i);
                    questions.set(i, temp1);
                    questions.set(j, temp2);
                    break;
                }
            }
        questions.add(testCode);    //add TestCode after all
        return questions;
    }
    public ArrayList GetExamQuestions(int testType,String pathXML) throws SQLException, IOException
    {
        Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE
                    ,ResultSet.CONCUR_UPDATABLE);
        Random r = new Random();
        ResultSet rs = st.executeQuery("Select * from DeThi where MaLoai = "+testType );
        int count = 0;
        while(rs.next())
        {
            count++;        //Count rows in result set
        }
        if(count == 0)
        {
            return null;
        }
        int randomInt = r.nextInt(count) + 1;
        rs.absolute(randomInt);     //Move cursor to randomInt-(th) row
        int code = rs.getInt("MaDe");
        String[] qIndex = (rs.getString("CauHoi")).split(" ");
        for(int i = 0; i < qIndex.length; i++)
        {
            System.out.println(Integer.parseInt(qIndex[i]));
        }
        
        //Parse XML to retrieve question in qIndex
        SaxProcessGetQuestion sax=new SaxProcessGetQuestion(qIndex);//tao mot doi tuong cua saxprocess. 
        SAXParserFactory saxpaser=SAXParserFactory.newInstance(); 
        try { 
            SAXParser parser = saxpaser.newSAXParser(); 
            File F=new File(pathXML); 
            parser.parse(F, sax); 
        } catch (ParserConfigurationException ex) { 
            
        } catch (SAXException ex) { 
            
        }  
        ArrayList questions = sax.GetQuestion();
        if(questions == null)
        {
            System.out.println("Not Found");
            return null;
        }

        int fCount = 0;
        for(int i = 0; i < qIndex.length; i++)
            for(int j = 0;j < questions.size(); j++)
            {
                if(j==i)
                    continue;
                Question temp1 = (Question) questions.get(j);
                if(qIndex[i].equals(temp1.code))
                {
                    Question temp2 = (Question) questions.get(i);
                    questions.set(i, temp1);
                    questions.set(j, temp2);
                    break;
                }
            }
        questions.add(code);    //add TestCode after all
        return questions;
    }
    public int UpdateDeThi(String testCode, String questions,String testType) throws SQLException
    {
        Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        int num = 0;
        num = st.executeUpdate("Insert into DeThi(MaDe,CauHoi,MaLoai) values('"+testCode+"','"+questions+"','"+testType+"')");
        return num;               
    }
    public int InitRandomTest(String pathBankQuestion,int testType, int numEasy,int numNormal,int numDifficult) throws IOException, SQLException
    {
        System.out.println("****Initing Random Test****");
        Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = st.executeQuery("Select MAX(MaDe) as MaDe from DeThi");
        int maxCodeTest = 0;
        while(rs.next())
        {
            maxCodeTest = rs.getInt("MaDe");
        }
        int[] qEasy = new int[numEasy];
        int[] qNormal = new int[numNormal];
        int[] qDifficult = new int[numDifficult];
        boolean flagDuplicate = false;
        BankQuestion bQ = new BankQuestion();
        bQ.CountNumberAndMaxQuestion(pathBankQuestion);
        int maxCodeQuestion = bQ.GetMaxCode();
        ArrayList aEQuestion = bQ.GetEasyQuestion();
        ArrayList aNQuestion = bQ.GetNormalQuestion();
        ArrayList aDQuestion = bQ.GetDifficultQuestion();
        Random r = new Random();               
        System.out.println("MaxCodeTest: " + maxCodeTest);
        System.out.println("MaxCodeQuestion: " + maxCodeQuestion);        
        String str = "";
        //Random Easy Question        
        for(int i = 0; i < numEasy; i++)
        {
            //qEasy[i] = (int) aEQuestion.get(r.nextInt(aEQuestion.size()));
            Object temp = aEQuestion.get(r.nextInt(aEQuestion.size()));
            qEasy[i] = Integer.parseInt(temp.toString());
            for(int j = 0; j < i; j++)
                if(qEasy[j] == qEasy[i])
                {
                    i--;
                    flagDuplicate = true; 
                    break;
                }
            if(flagDuplicate)
            {
                flagDuplicate = false;
                continue;
            }
            str += i==0?qEasy[i]:" "+qEasy[i];
        }
        //Random Normal Question
        for(int i = 0; i < numNormal; i++)
        {
            //qNormal[i] = (int) aNQuestion.get(r.nextInt(aNQuestion.size()));
            Object temp = aNQuestion.get(r.nextInt(aNQuestion.size()));
            qNormal[i] = Integer.parseInt(temp.toString());
            for(int j = 0; j < i; j++)
                if(qNormal[j] == qNormal[i])
                {
                    i--;
                    flagDuplicate = true; 
                    break;
                }
            if(flagDuplicate)
            {
                flagDuplicate = false;
                continue;
            }
            str += " "+qNormal[i];
        }
        //Random Difficult Question
        for(int i = 0; i < numDifficult; i++)
        {
            //qDifficult[i] = (int) aDQuestion.get(r.nextInt(aDQuestion.size()));
            Object temp = aDQuestion.get(r.nextInt(aDQuestion.size()));
            qDifficult[i] = Integer.parseInt(temp.toString());
            for(int j = 0; j < i; j++)
                if(qDifficult[j] == qDifficult[i])
                {
                    i--;
                    flagDuplicate = true; 
                    break;
                }
            if(flagDuplicate)
            {
                flagDuplicate = false;
                continue;
            }
            str += " "+qDifficult[i];            
        }
        //We have str already, let insert it to Database
        System.out.println(str);
        UpdateDeThi(Integer.toString(maxCodeTest+1), str, Integer.toString(testType));
        System.out.println("****Inited Random Test****");
        return maxCodeTest+1;
    }
    public ArrayList GetUser() throws SQLException
    {
        ArrayList d = new ArrayList();
        Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = st.executeQuery("Select * from ThiSinh");
        while(rs.next())
        {
            UserInfo u = new UserInfo();
            u.name = rs.getString("HoTen");
            u.identity = rs.getString("CMND");
            u.testCode = rs.getInt("MaDe");
            u.dayStart = rs.getDate("NgayBatDau");
            u.dayEnd = rs.getDate("NgayKetThuc");
            u.answers = rs.getString("CauTraLoi");
            u.score = rs.getInt("Diem");
            d.add(u);
        }
        return d;
    }
    public UserInfo GetUser(String identity,int testCode,String dayStart) throws SQLException
    {
        ArrayList d = new ArrayList();   
        Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = st.executeQuery("Select * from ThiSinh where CMND like '"+identity+"' and MaDe = "+testCode+" and NgayBatDau ='"+dayStart+"'");
        rs.next();
        UserInfo user = new UserInfo();
        user.name = rs.getString("HoTen");
        user.identity = rs.getString("CMND");
        user.testCode = rs.getInt("MaDe");
        user.dayStart = rs.getDate("NgayBatDau");
        user.dayEnd = rs.getDate("NgayKetThuc");
        user.answers = rs.getString("CauTraLoi");
        user.score = rs.getInt("Diem");
        return user;
    }
    public int UpdateUser(UserInfo u) throws SQLException
    {
        int num = 0;
        Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        num = st.executeUpdate("Insert into ThiSinh(HoTen,CMND,MaDe,NgayBatDau,NgayKetThuc,CauTraLoi,Diem) values('"
                +u.name+"','"+u.identity+"','"+u.testCode+"','"+u.dayStart+"','"+u.dayEnd+"','"+u.answers+"','"+u.score+"')");
        return num;
    }
}
